#!/usr/bin/env python
# -*- coding: utf-8 -*-
'''
Created on 2012/02/09

Module with the class that will calculate the required statistics using python

@author: João Vitor Machado Batista nº 5479
'''

import sqlite3

class getStatistics:

    def getYears(self):
        '''
            gets the years present in the database
        '''
        connection = sqlite3.connect('../database/RebidesDB.db')
        c = connection.cursor()
        c.execute(''' SELECT * FROM  database_years''')
        years = c.fetchall()
        c.close()
        
        return years

    def getTableByYear(self, year):
        '''
            creates a list with the database content of one year
        '''
        connection = sqlite3.connect('../database/RebidesDB.db')
        c = connection.cursor()
        c.execute(''' SELECT * FROM prof_data_year_{0} '''.format(year))
        databaseContent = c.fetchall()
        c.close()

        return databaseContent
        

    def getSchoolName(self, databaseContent):
        '''
            creates a school list from a given year
        '''
                                      
        schools = set([])

        for line in databaseContent:
            schools.add(line[8])
            pass        
        pass

        schools = list(schools)
        schools.sort()

        return schools

    def getCategorysNamePerSchool(self, school, databaseContent):
        '''
            creates a category list from a given school
            this speeds up the program
        '''
        
        categories = set([])
        
        for line in databaseContent:
            if(school == line[8]):
                categories.add(line[4])
                pass
            pass

        categories = list(categories)
        categories.sort()

        return categories
        

    def getTeachersNameByCategoryEstablisment(self, schoolName, profCategory, databaseContent):
        '''
            creates a teacher list from a given category and school
        '''
        
        teachers = set([])

        position = 0
        for line in databaseContent:
            if(schoolName == line[8]):
                if(profCategory == line[4]):
                    teachers.add(line[1])
                    pass
                pass
            pass
        pass

        
        teachers = list(set(teachers))
        teachers.sort()

        return teachers
        
    def getTeacherNameByDegreeTypeEstablismentCategory(self, schoolName, degreeType, profCategory, databaseContent):
        '''
            creates a teacher list from a given category, degree and school
            to deal with all the diferent ways that the degrees are in the database
                and to give the most accurate results only the first letter is compared
        '''

        teachers = set([])
        
        for line in databaseContent:
            if(schoolName == line[8]):
                if(degreeType == line[2][0]):
                    if(profCategory == line[4]):
                        teachers.add(line[1])
                        pass
                    pass
                pass
            pass
        pass

        teachers = list(teachers)
        teachers.sort()

        return teachers
